| | Complex Query Operators
https://learn.microsoft.com/en-us/ef/core/querying/complex-query-operators |
| Соединение таблиц с условием, с целью проверить, что во второй таблице нет записи из первой. | from t1 in <table1>
join t2 in <table2>
on new { Id = t1.Id, ConditionField = true }
equals new { Id = t2.ForeignId, ConditionField = t2.ConditionField }
into j
from r in j.DefaultIfEmpty()
where r == null
select t1.Id | SELECT t1.id
FROM
table1 AS t1
LEFT JOIN table2 AS t2
ON (t1.id = t2.foreign_id)
AND t2.condition_field
WHERE
(t2.id IS NULL) | Нет подзапросов и коррелированных запросов. Получен наиболее эффективный sql запрос. | from t1 in <table1>
from t2 in <table2>
.Where(e => t1.Id == e.ForeignId && e.ConditionField == true)
.DefaultIfEmpty()
where t2 == null
select t1.Id | SELECT t1.id
FROM
table1 AS t1
LEFT JOIN (
SELECT
t2.id,
t2.foreign_id
FROM
table2 AS t2
WHERE
t2.condition_field
) AS t ON t1.id = t.foreign_id
WHERE
(t.id IS NULL) | Пока нет проверки t2.ConditionField, запрос корректный, но при добавлении проверки мы получаем подзапрос. | <table1>
.Where(
e => !<table2>
.Where(e => e.ConditionField)
.Select(e => e.ForeignId)
.Contains(e.Id)
) | SELECT t1.id
FROM table1 AS t1
WHERE
NOT (
EXISTS (
SELECT
1
FROM
table2 AS t2
WHERE
t2.condition_field
AND (t2.foreign_id = t1.id)
)
) | Получаем коррелированный запрос. |
|
| Несколько соединений с условием | from t1 in <table1>
join t21 in <table2>
on new { Id = t1.Id, ConditionField = true }
equals new { Id = t21.ForeignId, ConditionField = t21.ConditionField }
into j1
from r1 in j1.DefaultIfEmpty()
join t22 in <table2>
on new { Id = t1.Id, ConditionField = true }
equals new { Id = t22.ForeignId, ConditionField = t22.ConditionField }
into j2
from r2 in j2.DefaultIfEmpty()
where r1 != null && r2 == null
select t1.Id | SELECT
t1.id
FROM
table1 AS t1
LEFT JOIN table2 AS t21 ON (t1.id = t21.foreign_id)
AND t21.condition_field
LEFT JOIN table2 AS t22 ON (t1.id = t22.foreign_id)
AND NOT (t22.condition_field)
WHERE
(t21.id IS NOT NULL)
AND (t22.id IS NULL) |
|
| Соединение DbSet и локальной коллекции. | Выборка данных на основе локальной коллекции |
| Соединение таблиц с условием, отличным от равенства | Greater Than Condition in Linq Join
https://stackoverflow.com/questions/3725032/greater-than-condition-in-linq-join | | from e in entity.M_Employee
from p in entity.M_Position.Where(p => e.PostionId >= p.PositionId)
select p; | Генерирует условие в join. | var query = from e in entity.M_Employee
from p in entity.M_Position
where e.PostionId >= p.PositionId
select p; | Подставляет условие в блок where (скорее всего оптимизатор поймет, но не совсем то). |
|
